package com.xzzz.common.db;

import ch.qos.logback.classic.Level;
import ch.qos.logback.classic.LoggerContext;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.setting.Setting;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.xzzz.common.base.util.DateUtils;
import com.xzzz.common.base.util.json.JsonUtil;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.File;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * https://github.com/modood/Administrative-divisions-of-China
 */
public class AdministrativeUtil {
    private static final String path = "C:\\WangYunFei\\GitCode-Outsource\\outsource\\common\\common-db\\src\\main\\resources\\administrative";
    private static final String now = DateUtils.now();
    static DataSource ds;

    private static final Integer batchCount = 200;

    private static void create() {
        LoggerContext loggerContext = (LoggerContext) LoggerFactory.getILoggerFactory();
        List<ch.qos.logback.classic.Logger> loggerList = loggerContext.getLoggerList();
        loggerList.forEach(logger -> logger.setLevel(Level.INFO));
        Setting setting = new Setting(path + "\\db.setting");
        ds = DSFactory.create(setting).getDataSource();
    }

    public static void main(String[] args) throws SQLException {
        long start = System.currentTimeMillis();
        create();
        List<String> sqlList = new ArrayList<>();
        sqlList.addAll(buildSQL("provinces.json", "none", "1"));
        sqlList.addAll(buildSQL("cities.json", "provinceCode", "2"));
        sqlList.addAll(buildSQL("areas.json", "cityCode", "3"));
        sqlList.addAll(buildSQL("streets.json", "areaCode", "4"));
        for (String sql : sqlList) {
            // System.out.println(sql);
            Db.use(ds).execute(sql);
        }
        System.out.printf(
                "\n\n共耗时：%s s" +
                "\n请使用以下SQL进行数据校验:\n" +
                "\033[%d;%dm%s\033[0m\n\n",
                (System.currentTimeMillis() - start) / 1000.0,
                36,4,
                "select ad_type,count(*) from base_sys_ad group by ad_type;"
        );
    }

    /**
     * 读取文件, 并拼接成插入SQL
     *
     * @param fileName  文件名
     * @param parentKey 上级行政区的字段KEY
     * @param type      行政级别类型:
     *                  1:省
     *                  2:市
     *                  3:区
     *                  4:县
     * @return 批量SQL
     */
    public static List<String> buildSQL(String fileName, String parentKey, String type) {
        List<String> sqlList = new ArrayList<>();
        ArrayNode adList = (ArrayNode) JsonUtil.toJsonNode(getFileContent(fileName));
        System.out.println(type + "：" + adList.size());

        StringBuilder batch = new StringBuilder();

        for (int i = 0; i < adList.size(); i++) {

            JsonNode ad = adList.get(i);
            String code = ad.get("code").asText();
            String parent = ad.get(parentKey) == null ? "0" : ad.get(parentKey).asText();
            String name = ad.get("name").asText();
            batch.append(formatSql(code, parent, name, type));

            // 每 batchCount 条为一条新增语句
            if (i != 0 && i % batchCount == 0) {
                sqlList.add("insert into base_sys_ad values " + batch.append(";"));
                batch = new StringBuilder();
            } else if (i == adList.size() - 1) {
                sqlList.add("insert into base_sys_ad values " + batch.append(";"));
            } else {
                batch.append(",");
            }
        }
        return sqlList;
    }

    /**
     * 获取文件内容
     */
    private static String getFileContent(String fileName) {
        File file = FileUtil.newFile(path + "\\" + fileName);
        return FileUtil.readString(file, StandardCharsets.UTF_8);
    }

    /**
     * 拼接成 insert sql 的 values 后面的部分
     *
     * @param code   行政区划代码
     * @param parent 上级行政区划代码
     * @param name   名称
     * @param type   行政级别类型
     */
    private static String formatSql(String code, String parent, String name, String type) {
        if (StrUtil.isBlank(parent)) {
            parent = "0";
        }
        return String.format("(null,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\")",
                code, parent, name, type, now);
    }
}
